﻿/***********************************************************************************************************************************
 *    Name: Backup_Permissions.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Grant minimal permissions necessary to backup & restore databases.
**/
USE master;
IF SUSER_ID('BBAAviation\Svc_VM_SQL_Bkup') IS Null CREATE LOGIN [BBAAviation\Svc_VM_SQL_Bkup] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [BBAAviation\Svc_VM_SQL_Bkup];
GRANT CREATE DATABASE TO [BBAAviation\Svc_VM_SQL_Bkup];
EXECUTE dbo.sp_addsrvrolemember N'BBAAviation\Svc_VM_SQL_Bkup', N'dbcreator';

-- permissions for backup on each database
DECLARE @tsql NVARCHAR(MAX)= N'';
SELECT @tsql+= N' USE [' + name + ']; CREATE USER [BBAAviation\Svc_VM_SQL_Bkup] FROM LOGIN [BBAAviation\Svc_VM_SQL_Bkup] WITH DEFAULT_SCHEMA= [dbo]; EXECUTE sys.sp_addrolemember @rolename= ''db_backupoperator'', @membername= ''BBAAviation\Svc_VM_SQL_Bkup'';'
  FROM sys.databases d WHERE name <> N'TempDB';
EXECUTE (@tsql);

GO